package stephen.com.dao;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import stephen.com.constant.Const;
import stephen.com.dao.base.BaseDao;
import stephen.com.model.User;
import stephen.com.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserDao extends BaseDao<User> {

	/**
	 * 用户登录的sql
	 *
	 * @param username 用户名
	 * @return User对象
	 */
	public User findUserByName(String username) {
		QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
		//开启下划线->驼峰转换所用
		RowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
		User user = null;
		try {
			user = qr.query("select * from user where username = ? limit 1", new BeanHandler<>(User.class,
					processor), username);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	/**
	 * 获取用户基本信息的sql
	 *
	 * @param userId 用户ID
	 * @return Map对象
	 */
	public Map<String, Object> findUserById(Integer userId) {
		QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
		Map<String, Object> map = null;
		try {
			String sql =
					"select u.id,u.username,u.dept_id as deptId,d.`name` as deptName " + "from user u " +
							"left join dept d on u.dept_id = d.id " + "where u.id = ?";

			map = qr.query(sql, new MapHandler(), userId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	/**
	 * 用户登录的sql
	 *
	 * @param email 邮箱
	 * @return User对象
	 */
	public User findUserByEmail(String email) {
		QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
		//开启下划线->驼峰转换所用
		RowProcessor processor = new BasicRowProcessor(new GenerousBeanProcessor());
		User user = null;
		try {
			user = qr.query("select * from user where email = ? limit 1", new BeanHandler<>(User.class,
					processor), email);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	/**
	 * 分页查询用户列表信息(userId,realName,age,gender,introduce,username,ufId)
	 *
	 * @param page 当前页数
	 * @return
	 */
	public List<Map<String, Object>> findList(Integer userId, int page, String search) {
		QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
		List<Map<String, Object>> maps = new ArrayList<>();
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select u.id as userId, ui.real_name as realName,");
			sql.append("ui.age,ui.gender,ui.description as introduce,u.username,uf.id as ufId ");
			sql.append("from user_info ui ");
			sql.append("left join user u on ui.user_id = u.id ");
			sql.append("left join user_focus uf on uf.user_focus_id = u.id and uf.user_id = ? ");
			if (null == search) {
				sql.append("order by u.id limit ?,? ");
				maps = qr.query(sql.toString(), new MapListHandler(), userId, (page - 1) * Const.PAGE_GROUP_COUNT,
						Const.PAGE_GROUP_COUNT);
			} else {
				sql.append("where ui.real_name like ? or u.username like ? ");
				sql.append("order by u.id limit ?,? ");
				search = search + "%";
				maps = qr.query(sql.toString(), new MapListHandler(), userId, search, search, (page - 1) * Const.PAGE_GROUP_COUNT, Const.PAGE_GROUP_COUNT);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return maps;
	}

	/**
	 * 统计查询到的用户列表的记录总数并获取这个数值
	 *
	 * @return 用户总数
	 */
	public long getSumUsers() {
		long sum = 0L;
		QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
		try {
			String sql = "select count(*) as sum from user_info ui left join user u on ui.user_id = u.id";
			Map<String, Object> map = qr.query(sql, new MapHandler());
			sum = Long.parseLong(map.get("sum").toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sum;
	}
}
